<?php
    class ModCp_Model_Product extends Zend_Db_Table_Abstract
    {
        protected $_name = 'product';
        protected $_primary = 'product_id';
        protected $_db;
        
        public function __construct()
        {
            $this->_db = Zend_Registry::get('db');
        }
        
        public function listProduct()
		{
		    $product = array(
                'pd.product_id', 
                'pd.product_name',
                'pd.product_image', 
                'pd.hot',
                'pd.status',
                'YEAR(pd.expiry_day) as expiry_year', 
                'MONTH(pd.expiry_day) as expiry_month', 
                'DAY(pd.expiry_day) as expiry_day', 
                'HOUR(pd.expiry_day) as expiry_hour', 
                'MINUTE(pd.expiry_day) as expiry_minute', 
                'SECOND(pd.expiry_day) as expiry_second',
            );
            $area = array(
                'a.area_name',
            );
            $provider = array(
                'pv.provider_name', 
            );
            $category = array(
                'cat.cat_name',
            );
            $sql = $this->_db->select()
                        ->from('product as pd', $product)
                        ->joinLeft('area as a', 'pd.area_id = a.area_id', $area)
                        ->joinLeft('provider as pv', 'pd.provider_id = pv.provider_id', $provider)
                        ->joinLeft('category as cat', 'pd.cat_id = cat.cat_id', $category)
                        ->order('product_id DESC');  
			$result = $this->_db->fetchAll($sql);
			return $result;
		}

        public function checkProduct($product_name, $product_id = '')
		{
			if($product_id != '')
			{
				$sql = $this->_db->select()
								->from($this->_name)
								->where('product_name = ? AND product_id != ?', $product_name, $product_id);
				$result = $this->_db->fetchAll($sql);
				return count($result);
			}
			else
			{
				$sql = $this->_db->select()
								->from($this->_name)
								->where('product_name = ?', $product_name);
				$result = $this->_db->fetchAll($sql);
				return count($result);
			}
		}
        
        public function getProductById($product_id)
        {
            $sql = $this->_db->select()
                            ->from($this->_name)
                            ->where('product_id = ?', $product_id);
            $result = $this->_db->fetchRow($sql);
            return $result;
        }
        
        public function addProduct($user_id, $cat_id, $area_id, $provider_id, $product_name, $sort_name, $product_image, $product_advantages, $product_conditions, $product_content, $product_link, $original_price, $sales_price, $expiry_day, $hot, $advantages_plaintext, $conditions_plaintext, $content_plaintext)
        {
            $data = array(
                'user_id' => $user_id,
                'cat_id' => $cat_id,
                'area_id' => $area_id,
                'provider_id' => $provider_id,
                'product_name' => $product_name,
                'sort_name' => $sort_name,
                'product_image' => $product_image,
                'product_advantages' => $product_advantages,
                'product_conditions' => $product_conditions,
                'product_content' => $product_content,
                'product_link' => $product_link,
                'original_price' => $original_price,
                'sales_price' => $sales_price,
                'expiry_day' => $expiry_day,
                'advantages_plaintext' => $advantages_plaintext,
                'conditions_plaintext' => $conditions_plaintext,
                'content_plaintext' => $content_plaintext,
                'hot' => $hot,
                'status' => 0,
            );
            $result = $this->_db->insert($this->_name, $data);
            if($result)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        
        public function editProduct($product_id, $cat_id, $area_id, $provider_id, $product_name, $sort_name, $product_image = '', $product_advantages, $product_conditions, $product_content, $product_link, $original_price, $sales_price, $expiry_day, $hot, $advantages_plaintext, $conditions_plaintext, $content_plaintext, $status)
        {
            $where = 'product_id = ' . $product_id;
            if($product_image != '')
            {
                $data = array(
                'cat_id' => $cat_id,
                'area_id' => $area_id,
                'provider_id' => $provider_id,
                'product_name' => $product_name,
                'sort_name' => $sort_name,
                'product_image' => $product_image,
                'product_advantages' => $product_advantages,
                'product_conditions' => $product_conditions,
                'product_content' => $product_content,
                'product_link' => $product_link,
                'original_price' => $original_price,
                'sales_price' => $sales_price,
                'expiry_day' => $expiry_day,
                'hot' => $hot,
                'advantages_plaintext' => $advantages_plaintext,
                'conditions_plaintext' => $conditions_plaintext,
                'content_plaintext' => $content_plaintext,
                'status' => $status,
                );
                $result = $this->_db->update($this->_name, $data, $where);
                if($result)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                $data = array(
                'cat_id' => $cat_id,
                'area_id' => $area_id,
                'provider_id' => $provider_id,
                'product_name' => $product_name,
                'sort_name' => $sort_name,
                'product_advantages' => $product_advantages,
                'product_conditions' => $product_conditions,
                'product_content' => $product_content,
                'product_link' => $product_link,
                'original_price' => $original_price,
                'sales_price' => $sales_price,
                'expiry_day' => $expiry_day,
                'hot' => $hot,
                'advantages_plaintext' => $advantages_plaintext,
                'conditions_plaintext' => $conditions_plaintext,
                'content_plaintext' => $content_plaintext,
                'status' => $status,
                );
                $result = $this->_db->update($this->_name, $data, $where);
                if($result)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        
        public function deleteProduct($product_id)
        {
            $where = 'product_id = ' . $product_id;
			$query = $this->_db->select()
                            ->from($this->_name)
							->where($where);
			$row = $this->_db->fetchRow($query);
			if (is_file(PUBLIC_PATH . '/files/product/' . $row['product_image']))
			{
				unlink(PUBLIC_PATH . '/files/product/' . $row['product_image']);
			}
			$result = $this->_db->delete($this->_name, $where);
			if($result)
			{
				return true;
			}
			else
			{
				return false;
			}
        }
        
        public function deleteProducts($listId)
        {
            foreach ($listId as $value)
			{
				$where = 'product_id = ' . $value;
				$query = $this->_db->select()
							->from($this->_name)
							->where($where);
				$row = $this->_db->fetchRow($query);
				if (is_file(PUBLIC_PATH . '/files/product/' . $row['product_image']))
				{
					unlink(PUBLIC_PATH . '/files/product/' . $row['product_image']);
				}
				$result = $this->_db->delete($this->_name, $where);
			}
        }
        
        public function searchProduct($keyword)
        {
            $where = "MATCH(product_name, advantages_plaintext, conditions_plaintext, content_plaintext) AGAINST('" . $keyword . "' IN NATURAL LANGUAGE MODE)";
            $product = array(
                'pd.product_id', 
                'pd.product_name',
                'pd.product_image', 
                'pd.hot',
                'pd.status',
                'YEAR(pd.expiry_day) as expiry_year', 
                'MONTH(pd.expiry_day) as expiry_month', 
                'DAY(pd.expiry_day) as expiry_day', 
                'HOUR(pd.expiry_day) as expiry_hour', 
                'MINUTE(pd.expiry_day) as expiry_minute', 
                'SECOND(pd.expiry_day) as expiry_second',
            );
            $area = array(
                'a.area_name',
            );
            $provider = array(
                'pv.provider_name', 
            );
            $category = array(
                'cat.cat_name',
            );
            $sql = $this->_db->select()
                        ->from('product as pd', $product)
                        ->joinLeft('area as a', 'pd.area_id = a.area_id', $area)
                        ->joinLeft('provider as pv', 'pd.provider_id = pv.provider_id', $provider)
                        ->joinLeft('category as cat', 'pd.cat_id = cat.cat_id', $category)
                        ->where($where)
                        ->order('product_id DESC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function addHotProducts($listId)
        {
            foreach ($listId as $value)
			{
				$where = 'product_id = ' . $value;
                $sql = $this->_db->select()
                                ->from($this->_name)
                                ->where($where);
                $row = $this->_db->fetchRow($sql);
                if($row['hot'] == '1')
                {
                    $data = array(
                        'hot' => 0,
                    );
                }
                else
                {
                    $data = array(
                        'hot' => 1,
                    );
                }
                $result = $this->_db->update($this->_name, $data, $where);
			}
        }
        
        public function convertToMysql($date)
        {
            $date = explode("-",$date);
            if ($date[0]<=9) { $date[0]="0".$date[0]; }
            if ($date[1]<=9) { $date[1]="0".$date[1]; }
            $date = array($date[2], $date[1], $date[0]);
            return $n_date=implode("-", $date);
        }
        
        public function convertToTime($date)
        {
            $date = explode("-",$date);
            $date = array($date[2], $date[1], $date[0]);
            return $n_date=implode("-", $date);
        }
        
        public function formatMoney($number, $fractional=false) {
			if ($fractional) {
				$number = sprintf('%f', $number);
			}
			while (true) {
				$replaced = preg_replace('/(-?\d+)(\d\d\d)/', '$1.$2', $number);
				if ($replaced != $number) {
					$number = $replaced;
				} else {
					break;
				}
			}
			return $number;
		}
        
        public function cut_string($str,$len,$more){
			if ($str=='' || $str==NULL) return $str;
			if (is_array($str)) return $str;
			$str = trim($str);
			if (strlen($str) <= $len) return $str;
			$str = substr($str,0,$len);
			if ($str != '') {
				if (!substr_count($str," ")) {
					if ($more) $str .= " ...";
					return $str;
				}
				while(strlen($str) && ($str[strlen($str)-1] != " ")) $str = substr($str,0,-1);
				$str = substr($str,0,-1);
				if ($more) $str .= " ...";
			}
			return $str;
		}
    }
